/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package da;

import entities.LoanType;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Bich Tran Van
 */
public class DALoanType {
    
    MSSQLConnection conn= new MSSQLConnection();;
     
    public DALoanType(){
            conn = new MSSQLConnection();
    }
    
    // get all loan type
    public ArrayList<LoanType> getEnable() throws Exception
    {
        ArrayList<LoanType> list = new ArrayList<LoanType>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL View_all_LoanType_Enable}");
            conn.rs = conn.cstm.executeQuery();
            while(conn.rs.next()){
                LoanType loanType  =new LoanType();
                loanType.setLoanTypeID(conn.rs.getInt("LoanTypeID"));
                loanType.setTypeOfLoan(conn.rs.getString("TypeOfLoan"));
                loanType.setInterestRate(conn.rs.getFloat("InterestRate"));
                loanType.setStatus(conn.rs.getBoolean("Status"));
                list.add(loanType);
            }
            conn.myClose();              
        } catch (Exception e) {
            
           e.printStackTrace();
        }
        return list;
    }
    
    public ArrayList<LoanType> getAll() throws Exception
    {
        ArrayList<LoanType> list = new ArrayList<LoanType>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL View_all_LoanType}");
            conn.rs = conn.cstm.executeQuery();
            while(conn.rs.next()){
                LoanType loanType  =new LoanType();
                loanType.setLoanTypeID(conn.rs.getInt("LoanTypeID"));
                loanType.setTypeOfLoan(conn.rs.getString("TypeOfLoan"));
                loanType.setInterestRate(conn.rs.getFloat("InterestRate"));
                loanType.setStatus(conn.rs.getBoolean("Status"));
                list.add(loanType);
            }
            conn.myClose();              
        } catch (Exception e) {
            
           e.printStackTrace();
        }
        return list;
    }
    
    //get loan type by id
    public ArrayList<LoanType> getByID(int id) throws Exception
    {
        ArrayList<LoanType> list = new ArrayList<LoanType>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL View_LoanType_By_ID(?)}");
            conn.cstm.setInt(1, id);
            conn.rs = conn.cstm.executeQuery();
            while(conn.rs.next()){
                LoanType loanType  =new LoanType();
                loanType.setLoanTypeID(conn.rs.getInt("LoanTypeID"));
                loanType.setTypeOfLoan(conn.rs.getString("TypeOfLoan"));
                loanType.setInterestRate(conn.rs.getFloat("InterestRate"));
                loanType.setStatus(conn.rs.getBoolean("Status"));
                list.add(loanType);
            }
            conn.myClose();              
        } catch (Exception e) {
           e.printStackTrace();
        }
        return list;
    }
    //get loan type by name
    public ArrayList<LoanType> getByName(String name ) throws Exception
    {
        ArrayList<LoanType> list = new ArrayList<LoanType>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL View_LoanType_By_Name(?)}");
            conn.cstm.setString(1, name);
            conn.rs = conn.cstm.executeQuery();
            while(conn.rs.next()){
                LoanType loanType  =new LoanType();
                loanType.setLoanTypeID(conn.rs.getInt("LoanTypeID"));
                loanType.setTypeOfLoan(conn.rs.getString("TypeOfLoan"));
                loanType.setInterestRate(conn.rs.getFloat("InterestRate"));
                loanType.setStatus(conn.rs.getBoolean("Status"));
                list.add(loanType);
            }
            conn.myClose();              
        } catch (Exception e) {
           e.printStackTrace();
        }
        return list;
        
    }
    
    //insert to Loan type
    public int insert(String typeOfLoan, float interestRate) throws Exception
    {
        
        int result = 0;
        try
        {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL Insert_LoanType(?,?)}");
            conn.cstm.setString(1,typeOfLoan);
            conn.cstm.setFloat(2,interestRate);
            result = conn.cstm.executeUpdate();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return result;
    }
    
    //update to loan type
     public int update(LoanType loanType) throws Exception
     {
        int result = 0;
        try
        {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL Update_LoanType(?,?,?)}");
            conn.cstm.setInt(1,loanType.getLoanTypeID());
            conn.cstm.setString(2,loanType.getTypeOfLoan());
            conn.cstm.setDouble(3,loanType.getInterestRate());
            result = conn.cstm.executeUpdate();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return result;
    }
    
    //delete from loan type
    public int disable(int id) throws Exception
    {
        int result = 0;
        try
        {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL Disable_LoanType(?)}");
            conn.cstm.setInt(1,id);
            result = conn.cstm.executeUpdate();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return result;
    }

     public int enable(int id) throws Exception
     {
        int result = 0;
        try
        {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL Enable_LoanType(?)}");
            conn.cstm.setInt(1,id);
            result = conn.cstm.executeUpdate();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return result;
     }
}
